#!/usr/bin/env python
# encoding: utf-8

import hashlib,time,random,decimal,json
from app import db

from app.model import User
from app.model import Electronic
from app.model import ElectronicLog
from app.model import ElectronicOrder
from app.model import ElectronicOrderItem
from app.common.libs.Helper import getCurrentDate, getFormatDate
from sqlalchemy import func, desc, or_

class OrderService():

    @staticmethod
    def geneSn():
        sn = None
        while True:
            sn = time.strftime("%Y%m%d%H%M%S", time.localtime()) + '%s'%random.randint( 0,9999 )
            if not ElectronicOrder.query.filter_by( sn = sn  ).first():
                break
        return sn

    @staticmethod
    def outStock(items=[], receiver=''):
        resp = {'code': 20000, 'message': '出库成功~', 'data': {}}
        electronic_ids = []
        for item in items:
            electronic_ids.append( item['electronic_id'] )
        try:
            #为了防止并发库存出问题了，我们坐下select for update
            electronic_list = db.session.query( Electronic ).filter( Electronic.id.in_( electronic_ids ) )\
                .with_for_update().all()
            stock_mapping = {}
            for electronic in electronic_list:
                stock_mapping[electronic.id] = electronic.stock
            for item in items:
                stock =  stock_mapping[item['electronic_id']]
                if int( item['count'] ) > int( stock ):
                    raise Exception( "库存不足~~(元器件:%s, 库存：%s, 出库数:%s)"%(item['electronic_name'], stock, item['count'] ) )
                tmp_ret = Electronic.query.filter_by( id = item['electronic_id'] )\
                                        .update({ "stock":int(stock) - int(item['count']) })
                if not tmp_ret:
                    raise Exception("出库失败")
                #  log = ElectronicLog()
                #  log.type = 2
                #  log.receiver = receiver
                #  log.receiver_id = int(item['receiver_id'])
                #  log.operater_id = int(item['operater_id'])
                #  log.count = int(item['count'])
                #  log.electronic_id = int(item['electronic_id'])
                #  log.created_time = getCurrentDate()
                #  db.session.add( log )
            db.session.commit()
        except Exception as e:
            db.session.rollback()
            resp['code'] = -1
            resp['message'] = str(e)
        return resp

    @staticmethod
    def inStock(order_id=0):
        order_items = db.session.query(ElectronicOrderItem, Electronic)\
            .filter(ElectronicOrderItem.electronic_order_id == order_id)\
            .filter(ElectronicOrderItem.electronic_id == Electronic.id)\
            .all()
        items = [{
            'electronic_name':electronic.name,
            'electronic_id':item.electronic_id,
            'count':item.count
        } for item, electronic in order_items]

        resp = {'code': 20000, 'message': '入库成功~', 'data': {}}
        electronic_ids = []
        for item in items:
            electronic_ids.append( item['electronic_id'] )
        try:
            #为了防止并发库存出问题了，我们坐下select for update
            electronic_list = db.session.query( Electronic ).filter( Electronic.id.in_( electronic_ids ) )\
                .with_for_update().all()
            stock_mapping = {}
            for electronic in electronic_list:
                stock_mapping[electronic.id] = electronic.stock
            for item in items:
                stock =  stock_mapping[item['electronic_id']]
                #  if int( item['count'] ) > int( stock ):
                    #  raise Exception( "库存不足~~(元器件:%s, 库存：%s, 出库数:%s)"%(item['electronic_name'], stock, item['count'] ) )
                tmp_ret = Electronic.query.filter_by( id = item['electronic_id'] )\
                                        .update({ "stock":int(stock) + int(item['count']) })
                if not tmp_ret:
                    raise Exception("入库失败")
                #  log = ElectronicLog()
                #  log.type = 2
                #  log.receiver = receiver
                #  log.receiver_id = int(item['receiver_id'])
                #  log.operater_id = int(item['operater_id'])
                #  log.count = int(item['count'])
                #  log.electronic_id = int(item['electronic_id'])
                #  log.created_time = getCurrentDate()
                #  db.session.add( log )
            db.session.commit()
        except Exception as e:
            db.session.rollback()
            resp['code'] = -1
            resp['message'] = str(e)
        return resp

    @staticmethod
    def financeFetch(request):
        """ 财务查询订单 """
        resp = { 'code':20000,'message':'查询成功~', 'data':{}}
        req = request.values
        page = int( req['page'] ) if 'page' in req else 0
        limit = int( req['limit'] ) if 'limit' in req else 0
        offset = ( page - 1 ) * limit
        total = 0

        sn = req['sn'] if 'sn' in req else ''
        type = req['type'] if 'type' in req else 0
        status = req['status'] if 'status' in req else 0

        startTime = req['startTime'] if 'startTime' in req else ''
        endTime = req['endTime'] if 'endTime' in req else ''

        # 查询订单
        query = '''
                select o.id, o.sn, o.status, o.type, o.project, o.note, o.created_time, u1.nickname, u2.nickname
                from `user` as u1, `user` as u2, `electronic_order` as o
                where o.user_id=u1.id and o.checker_id=u2.id
                '''
        if startTime:
            query += " and o.created_time >= '%s' "%startTime
        if endTime:
            query += " and o.created_time <= '%s' "%endTime
        if sn:
            query += ' and o.sn=%s'%req['sn']
        if type:
            query += ' and o.type=%s'%req['type']
        if status:
            query += ' and o.status=%s'%req['status']
        # 时间倒序
        query += ' order by o.created_time desc'
        # 分页限制
        query += ' limit %s, %s'%(offset, limit)
        # 获取结果, 订单查询完成
        order_list = db.session.execute(query)
        # 查询订单 详情
        items = []
        for order in order_list:
            # 订单总数加1
            total += 1
            query = '''
                    select e.sn, e.name, e.package, e.spec, e.position, e.supplier, e.price, eoi.count
                    from `electronic` as e, `electronic_order` as eo, `electronic_order_item` as eoi
                    where e.id=eoi.electronic_id and eoi.electronic_order_id=eo.id and eo.id=%s;
                    ''' % order[0]
            order_item_list = db.session.execute(query).fetchall()
            #  order_items =
            items.append({
                'id':           order[0],
                'sn':           order[1],
                'status':       order[2],
                'type':         order[3],
                'project':      order[4],
                'note':         order[5],
                'created_time': getFormatDate(order[6]),
                'user':         order[7],
                'checker':      order[8],
                'items':  [{ 'sn':       item[0],
                            'name':     item[1],
                            'package':  item[2],
                            'spec':     item[3],
                            'position': item[4],
                            'supplier': item[5],
                            'price':    float(item[6]),
                            'count':    item[7],
                            'subtotal': float(item[6])*int(item[7]),
                            } for item in order_item_list],
                'totalAmount':  sum([float(tmp[6])*int(tmp[7]) for tmp in order_item_list])
            })
        resp['data']['items'] = items
        resp['data']['total'] = total
        return resp

    @staticmethod
    def researchFetch(request, user_info):
        """ 研发查询订单 """
        resp = { 'code':20000,'message':'查询成功~', 'data':{}}
        req = request.values
        page = int( req['page'] ) if 'page' in req else 0
        limit = int( req['limit'] ) if 'limit' in req else 0
        offset = ( page - 1 ) * limit

        sn = req['sn'] if 'sn' in req else ''
        type = req['type'] if 'type' in req else 0
        status = req['status'] if 'status' in req else 0

        query = db.session.query( ElectronicOrder, User )\
                .filter(ElectronicOrder.user_id == User.id)\
                .filter(User.id == user_info.id)
        if sn:
            query = query.filter(ElectronicOrder.sn.like('%'+sn+'%'))
        if status:
            query = query.filter(ElectronicOrder.status == status)
        if type:
            query = query.filter(ElectronicOrder.type == type)
        total = query.count()
        order_list = query.order_by(desc(ElectronicOrder.created_time)).offset( offset ).limit( limit ).all()
        items = [{
                'id':order.id,
                'sn':order.sn,
                'status':order.status,
                'type':order.type,
                'project':order.project,
                'note':order.note,
                'created_time':getFormatDate(item.created_time),
                'user': user.nickname
            } for order, user in order_list]
        resp['data']['items'] = items
        resp['data']['total'] = total
        return resp

    def stockFetch(request):
        """ 库管查询订单 """
        resp = { 'code':20000,'message':'查询成功~', 'data':{}}
        req = request.values
        page = int( req['page'] ) if 'page' in req else 0
        limit = int( req['limit'] ) if 'limit' in req else 0
        offset = ( page - 1 ) * limit

        sn = req['sn'] if 'sn' in req else ''
        type = req['type'] if 'type' in req else 0
        status = req['status'] if 'status' in req else 0

        query = db.session.query( ElectronicOrder, User )\
                .filter(ElectronicOrder.user_id == User.id)
        if sn:
            query = query.filter(ElectronicOrder.sn.like('%'+sn+'%'))
        if status:
            query = query.filter(ElectronicOrder.status == status)
        if type:
            query = query.filter(ElectronicOrder.type == type)
        total = query.count()
        order_list = query.order_by(desc(ElectronicOrder.created_time)).offset( offset ).limit( limit ).all()
        items = [{
                'id':order.id,
                'sn':order.sn,
                'status':order.status,
                'type':order.type,
                'project':order.project,
                'note':order.note,
                'created_time':getFormatDate(item.created_time),
                'user': user.nickname
            } for order, user in order_list]
        resp['data']['items'] = items
        resp['data']['total'] = total
        return resp

    def purchaseFetch(request):
        """ 库管查询订单 """
        resp = { 'code':20000,'message':'查询成功~', 'data':{}}
        req = request.values
        page = int( req['page'] ) if 'page' in req else 0
        limit = int( req['limit'] ) if 'limit' in req else 0
        offset = ( page - 1 ) * limit

        sn = req['sn'] if 'sn' in req else ''
        type = req['type'] if 'type' in req else 0
        status = req['status'] if 'status' in req else 0

        query = db.session.query( ElectronicOrder, User )\
                .filter(ElectronicOrder.user_id == User.id)
        if sn:
            query = query.filter(ElectronicOrder.sn.like('%'+sn+'%'))
        if status:
            query = query.filter(ElectronicOrder.status == status)
        if type:
            query = query.filter(ElectronicOrder.type == type)
        total = query.count()
        order_list = query.order_by(desc(ElectronicOrder.created_time)).offset( offset ).limit( limit ).all()
        items = [{
                'id':order.id,
                'sn':order.sn,
                'status':order.status,
                'type':order.type,
                'project':order.project,
                'note':order.note,
                'created_time':getFormatDate(order.created_time),
                'user': user.nickname
            } for order, user in order_list]
        resp['data']['items'] = items
        resp['data']['total'] = total
        return resp




